<?php

/**
 * This is the model class for table "zy_event_customer".
 *
 * The followings are the available columns in table 'zy_event_customer':
	 * @property integer $customer_id
	 * @property string $event_id
	 * @property string $event_code
	 * @property string $firstname
	 * @property string $lastname
	 * @property string $emailaddress
	 * @property string $postcode
	 * @property string $mobile
	 * @property integer $winning_status
	 * @property integer $sms_status
	 * @property string $ip
	 * @property string $step
	 * @property integer $is_delete
	 * @property string $remark
	 * @property string $create_user
	 * @property string $create_time
	 * @property string $update_user
	 * @property string $update_time
 */
class EventCustomer extends CActiveRecord
{
	/**
	 * Returns the static model of the specified AR class.
	 * @param string $className active record class name.
	 * @return EventCustomer the static model class
	 */
	public static function model($className=__CLASS__)
	{
		return parent::model($className);
	}

	/**
	 * @return string the associated database table name
	 */
	public function tableName()
	{
		return 'zy_event_customer';
	}
	public function primaryKey()
	{
		return 'customer_id';
	}

	/**
	 * @return array validation rules for model attributes.
	 */
	public function rules()
	{
		// NOTE: you should only define rules for those attributes that
		// will receive user inputs.
		return array(
			array('winning_status, sms_status, is_delete', 'numerical', 'integerOnly'=>true),
			array('event_id,partner_id, event_code, firstname, lastname, emailaddress, postcode, mobile, ip,lang, step,award_address_id, remark, create_user, update_user', 'length', 'max'=>255),
			array('create_time, update_time', 'safe'),
			// The following rule is used by search().
			// Please remove those attributes that should not be searched.
			array('customer_id, event_id,partner_id, event_code, firstname, lastname, emailaddress, postcode, mobile, winning_status, sms_status, ip, lang,step, award_address,is_delete, remark, create_user, create_time, update_user, update_time', 'safe', 'on'=>'search'),
		);
	}

	/**
	 * @return array relational rules.
	 */
	public function relations()
	{
		// NOTE: you may need to adjust the relation name and the related
		// class name for the relations automatically generated below.
		return array(
		);
	}

	/**
	 * @return array customized attribute labels (name=>label)
	 */
	public function attributeLabels()
	{
		return array(
			'customer_id' =>Yii::t("EventCustomer","Customer ID"),
			'event_id' =>Yii::t("EventCustomer","Event ID"),
			'partner_id'=>Yii::t("EventCustomer","Partner ID"),
			'event_code' =>Yii::t("EventCustomer","Event Code"),
			'firstname' =>Yii::t("EventCustomer","Firstname"),
			'lastname' =>Yii::t("EventCustomer","Lastname"),
			'emailaddress' =>Yii::t("EventCustomer","Emailaddress"),
			'postcode' =>Yii::t("EventCustomer","Postcode"),
			'mobile' =>Yii::t("EventCustomer","Mobile"),
			'winning_status' =>Yii::t("EventCustomer","Winning Status"),
			'sms_status' =>Yii::t("EventCustomer","Sms Status"),
			'ip' =>Yii::t("EventCustomer","Ip"),
			'lang' =>Yii::t("EventCustomer","Lang"),
			'step' =>Yii::t("EventCustomer","Step"),
			'award_address'=>Yii::t("EventCustomer","Award Address"),
			'is_delete' =>Yii::t("EventCustomer","Is Delete"),
			'remark' =>Yii::t("EventCustomer","Remark"),
			'create_user' =>Yii::t("EventCustomer","Create User"),
			'create_time' =>Yii::t("EventCustomer","Create Time"),
			'update_user' =>Yii::t("EventCustomer","Update User"),
			'update_time' =>Yii::t("EventCustomer","Update Time"),
		);
	}

	/**
	 * Retrieves a list of models based on the current search/filter conditions.
	 * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
	 */
	public function search()
	{
		// Warning: Please modify the following code to remove attributes that
		// should not be searched.
		$level = Yii::app ()->user->level ();
		$user_id = Yii::app ()->user->id;
		$criteria=new CDbCriteria;

		$criteria->compare('customer_id',$this->customer_id);
		$criteria->compare('event_id',$this->event_id,true);
		if ($level == 9) {
			$criteria->compare ( 'partner_id', $user_id );
		} else {
			$criteria->compare ( 'partner_id', $this->partner_id );
		}
		$criteria->compare('event_code',$this->event_code,true);
		$criteria->compare('firstname',$this->firstname,true);
		$criteria->compare('lastname',$this->lastname,true);
		$criteria->compare('emailaddress',$this->emailaddress,true);
		$criteria->compare('postcode',$this->postcode,true);
		$criteria->compare('mobile',$this->mobile,true);
		$criteria->compare('winning_status',$this->winning_status);
		$criteria->compare('sms_status',$this->sms_status);
		$criteria->compare('ip',$this->ip,true);
		$criteria->compare('lang',$this->lang,true);
		$criteria->compare('step',$this->step,true);
		$criteria->compare('award_address',$this->award_address,true);
		$criteria->compare('is_delete',$this->is_delete);
		$criteria->compare('remark',$this->remark,true);
		$criteria->compare('create_user',$this->create_user,true);
		$criteria->compare('create_time',$this->create_time,true);
		$criteria->compare('update_user',$this->update_user,true);
		$criteria->compare('update_time',$this->update_time,true);

		return new CActiveDataProvider($this, array(
			'criteria'=>$criteria,
			'sort'=>array(  
           		 'defaultOrder'=>'customer_id DESC',  
       		 ),  
       		'pagination'=>array(  
           		 'pageSize'=>20  
       		 ),  
		));
	}
	// 根据活动代码查询标题
	public function getActivetyCodeTitle() {
		//$level = yii::app ()->user->level ();
		$user_id = Yii::app ()->user->id;
//		if ($level == 1) {
//			$sql = "select * from zy_event";
//		} else {
			$sql = "select * from zy_event where user_id='" . $user_id . "'";
		//}
		$rs = Yii::app ()->db->createCommand ( $sql )->queryAll ();
		return CHtml::listData ( $rs, 'event_code', 'event_title' );
	}
		
	// 根据code获取此活动中的设置礼品数
	public function getGiftNumber($event_code, $user_id) {
		$sql = "select mobile from zy_event_customer where partner_id='" . $user_id . "' and event_code='" . $event_code . "' and winning_status = 0";
		$rs = Yii::app ()->db->createCommand ( $sql )->queryAll ();
		shuffle ( $rs ); // 打乱数组
		return $rs;
	}
	
	// 调查用户,取得ID
	public function getCuid($moblie, $user_id) {
		$sql = "select customer_id from zy_event_customer where partner_id='" . $user_id . "' and mobile='" . $moblie . "'";
		$rs = Yii::app ()->db->createCommand ( $sql )->queryAll ();
		return $rs [0] ['customer_id'];
	}
	
	// 更新已中奖用户记录的中奖字段
	public function setLotteryUserStatus($moblie, $user_id,$event_code) {
		$sql = "update zy_event_customer set `winning_status` = 1  where partner_id='" . $user_id . "' and mobile='" . $moblie . "' and event_code='".$event_code."'";
		$rs = Yii::app ()->db->createCommand ( $sql )->execute ();
		return $rs;
	}
	
	// 随机从zy_gift_password表中拿出一个密码
	public function getPassword($event_code, $user_id) {
		$sql = "select event_id from zy_event where user_id='" . $user_id . "' and event_code='" . $event_code . "'";
		$rs = Yii::app ()->db->createCommand ( $sql )->queryAll ();
		$sqls = "select gift_password_id,password from zy_event_gift_password where event_id='" . $rs [0] ['event_id'] . "' and partner_id='" . $user_id . "' and state= 0";
		$rss = Yii::app ()->db->createCommand ( $sqls )->queryAll ();
		array_unique ( $rss );
		$result = array_rand ( $rss, 1 );
		return $rss [$result];
	}
	
	 //发送邮件
	function sendEmail($email_to, $first_name,$last_name,$password,$adress,$lang,$emailtemplate) {
		$email_message = str_replace ( "{firstName}", $first_name, $emailtemplate);
		$email_message = str_replace ( "{lastName}",$last_name, $email_message );
		$email_message = str_replace ( "{awardCode}",$password, $email_message );
		$email_message = str_replace ( "{awardAddress}",$adress, $email_message );
		
		// $email_subject = Yii::t('base', 'Novi telecommunications activities confirmation code (via Novi telecommunications system (the default) to send)');
		if ($lang == "en") {
			$email_subject = 'Netcube - Please confirm your registration for our 1GB Free Broadband Data!';
		} else {
			$email_subject = 'Netcube - 请确认注册领取您的1GB免费无线上网流量。';
		}
		
		$headers = 'Content-type: text/html; charset=utf-8' . "\r\n";
		$headers .= 'From: Netcube<sales@netcube.com.au>' . "\r\n";
		$headers .= "Reply-To: " . strip_tags ( 'sales@netcube.com.au' ) . "\r\n";
		$headers .= 'X-Mailer: PHP/' . phpversion () . "\r\n";
		$i = @mail ( $email_to, $email_subject, $email_message, $headers );
	}
	
	// 更新短信发送成功用户记录的短信字段
	public function setSmsUserStatus($moblie, $user_id) {
		$sql = "update zy_event_customer set `sms_status` = 1 where partner_id='" . $user_id . "' and mobile='" . $moblie . "'";
		$rs = Yii::app ()->db->createCommand ( $sql )->execute ();
		return $rs;
	}
	
	// 改变zy_gift_password 中 拿出密码记录的状态
	public function setPasswordState($gift_password_id) {
		$sql = "update zy_event_gift_password set state = 1 where gift_password_id='" . $gift_password_id . "'";
		Yii::app ()->db->createCommand ( $sql )->execute ();
	}
	
	//通过密码查询密码记录ID
	public function setSuserId($gift_password_id,$customer_id){
		$sql = "update zy_event_gift_password set partner_id = '".$customer_id."' where gift_password_id='" . $gift_password_id . "'";
		Yii::app ()->db->createCommand ( $sql )->execute ();
	}
	
	
	// 切换中奖状态
	public function switchStatus($num) {
		if ($num != '') {
			if ($num == 1) {
				
				return Yii::t ( 'base', 'Have been winners' );
			} else {
				return Yii::t ( 'base', 'No winners' );
			}
		}
	}
	
	// 切换短信发送状态
	public function SmsSwitchStatus($num) {
		if ($num != '') {
			if ($num == 1) { // 发送成功
				return Yii::t ( 'base', 'Sent successfully' );
			} elseif ($num == 0) { // 未发送
				return Yii::t ( 'base', 'Unsent' );
			} else { // 发送失败
				return Yii::t ( 'base', 'Failed to send' );
			}
		}
	}
	
	
	/**
	 * 获取电话表中人员.
	 */
	public function getSurveyUser($code){
		//加载PHPexcel类文件
		Yii::$enableIncludePath = false;
		Yii::import('application.extensions.PHPExcel', 1);
		//获取活动标题
		$activityName = $this->getActivityName($code);
		$sql="SELECT * FROM zy_event_customer WHERE event_code='".$code."'";
		$rs = yii::app()->db->createCommand($sql)->queryAll();
	
		$objectPHPExcel = new PHPExcel();
		$objectPHPExcel->setActiveSheetIndex(0);
		//设置表头格式
		$objectPHPExcel->getActiveSheet()->getStyle('A1:J1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$objectPHPExcel->getActiveSheet()->getStyle('A1:J1')->getBorders()->getLeft()->getColor()->setARGB('FF993300');
		$objectPHPExcel->getActiveSheet()->getStyle('A1:J1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
		$objectPHPExcel->getActiveSheet()->getStyle('A1:J1')->getFill()->getStartColor()->setARGB('FFCCCCCC');
		$objectPHPExcel->getActiveSheet()->getStyle('A1:J1')->getFont()->setName('Candara');
		$objectPHPExcel->getActiveSheet()->getStyle('A1:J1')->getFont()->setSize(12);
		$objectPHPExcel->getActiveSheet()->getStyle('A1:J1')->getFont()->setBold(true);
		$objectPHPExcel->getActiveSheet()->getStyle('A1:J1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK);
		$objectPHPExcel->getActiveSheet()->getStyle('A1:J1')->getFont()->setBold(true);
		//报表头的输出
		$objectPHPExcel->getActiveSheet()->setCellValue('A1','Event');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
		$objectPHPExcel->getActiveSheet()->setCellValue('B1','First Name');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
		$objectPHPExcel->getActiveSheet()->setCellValue('C1','Last Name');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);
		$objectPHPExcel->getActiveSheet()->setCellValue('D1','Email');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(35);
		$objectPHPExcel->getActiveSheet()->setCellValue('E1','Postcode');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
		$objectPHPExcel->getActiveSheet()->setCellValue('F1','Mobile');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(35);
		$objectPHPExcel->getActiveSheet()->setCellValue('G1','Winning Status');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(25);
		$objectPHPExcel->getActiveSheet()->setCellValue('H1','Sms Status');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(25);
		$objectPHPExcel->getActiveSheet()->setCellValue('I1','IP');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(25);
		$objectPHPExcel->getActiveSheet()->setCellValue('J1','Award Address ID');
		$objectPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(25);
		
		for ($i=0; $i < count($rs); $i++){
			$winningStatus = $this->getReplaceAttribute($rs[$i][winning_status]);
			$smsStatus = $this->getSmsReplaceAttribute($rs[$i][sms_status]);
			
			$objectPHPExcel->getActiveSheet()->setCellValue('A'.($i+2),$activityName);
			$objectPHPExcel->getActiveSheet()->setCellValue('B'.($i+2),$rs[$i][firstname]);
			$objectPHPExcel->getActiveSheet()->setCellValue('C'.($i+2),$rs[$i][lastname]);
			$objectPHPExcel->getActiveSheet()->setCellValue('D'.($i+2),$rs[$i][emailaddress]);
			$objectPHPExcel->getActiveSheet()->setCellValue('E'.($i+2),$rs[$i][postcode]);
			$objectPHPExcel->getActiveSheet()->setCellValue('F'.($i+2),$rs[$i][mobile].' ');
			$objectPHPExcel->getActiveSheet()->setCellValue('G'.($i+2),$winningStatus);
			$objectPHPExcel->getActiveSheet()->setCellValue('H'.($i+2),$smsStatus);
			$objectPHPExcel->getActiveSheet()->setCellValue('I'.($i+2),$rs[$i][ip]);
			$objectPHPExcel->getActiveSheet()->setCellValue('J'.($i+2),$rs[$i][award_address_id]);
				
		}
	
		ob_end_clean();
		ob_start();
			
		header('Content-Type : application/vnd.ms-excel');
		header('Content-Disposition:attachment;filename="'.'Survey User Lists-'.date("j,m,Y").'.xls"');
		$objWriter= PHPExcel_IOFactory::createWriter($objectPHPExcel,'Excel5');
		$objWriter->save('php://output');
	}
	
	
	/**
	 * 通过活动代码,得到活动名称
	 */
	public function getActivityName($eventCode)
	{
		$rs=Event::model()->find('event_code=:event_code',array(':event_code'=>$eventCode));
		return $rs->event_title;
	}
	
		/**
	 * 替换数字对应的内容
	 */
	public function getReplaceAttribute($winningStatus){
		$attribute="";
		if($winningStatus == 0){
			$attribute="Not winning";
		}else if($winningStatus == 1){
			$attribute="Has been winning";
		}
		return $attribute;
	}
	
	/**
	 *替换短信发送状态 
	 */
	public function getSmsReplaceAttribute($attribute){
		$message="";
		if($attribute == 0){
			$message="Not Sent";
		}elseif ($attribute == 1){
			$message="Sent Successfully";
		}else{
			$message="Send Failed";
		}
		return $message;
	}
	
	//通过event_id找到该活动的邮件模板
	public function getEmail($event_id,$model)
	{
		if($model->lang=="en")
		{
		 $sql = "select winning_email_en from zy_event where event_id=" . $event_id . "";
		 $rs = Yii::app ()->db->createCommand ( $sql )->queryAll ();
		 $email=$rs [0] ['winning_email_en'];
		}else{
		 $sql = "select winning_email_cn from zy_event where event_id=" . $event_id . "";
		  $rs = Yii::app ()->db->createCommand ( $sql )->queryAll ();
		 $email=$rs [0] ['winning_email_cn'];	
		}
		return $email;
	}
	
	//通过event_id找到该活动的邮件模板标题
	public function getEmailTitle($event_id,$model)
	{
		if($model->lang=="en")
		{
		 $sql = "select winning_email_title_en from zy_event where event_id=" . $event_id . "";
		 $rs = Yii::app ()->db->createCommand ( $sql )->queryAll ();
		 $email=$rs [0] ['winning_email_title_en'];
		}else{
		 $sql = "select winning_email_title_en from zy_event where event_id=" . $event_id . "";
		  $rs = Yii::app ()->db->createCommand ( $sql )->queryAll ();
		 $email=$rs [0] ['winning_email_title_cn'];	
		}
		return $email;
	}

		//通过event_id找到该活动的短信模板
	public function getSms($event_id,$model)
	{
		if($model->lang=="en")
		{
		 $sql = "select winning_sms_en from zy_event where event_id=" . $event_id . "";
		 $rs = Yii::app ()->db->createCommand ( $sql )->queryAll ();
		 $sms=$rs [0] ['winning_sms_en'];	
		}else{
		 $sql = "select winning_sms_cn from zy_event where event_id=" . $event_id . "";
		 $rs = Yii::app ()->db->createCommand ( $sql )->queryAll ();
		 $sms=$rs [0] ['winning_sms_cn'];	
		}
		return $sms;
	}
	
	//通过event_id找到该活动下的最后一步
//	public function getEndStep($event_id)
//	{
//		$sql="SELECT MAX(step) FROM zy_event_step WHERE event_id=$event_id";
//		$step = Yii::app ()->db->createCommand ( $sql )->queryAll ();
//		return $step[0]['MAX(step)'];
//	}
}